﻿using System;
//using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.DataAccessProvider;
using DTN.Core;

namespace Hanel.DTN.News
{
    public class tblContentManager : ItblContentManager
    {
        private static ItblContentManager itblContentManager = null;

         private tblContentManager()
        {
        }
        public static ItblContentManager CreateInstance()
        {
            if (itblContentManager == null)
            {
                itblContentManager = new tblContentManager();
            }
            return itblContentManager;
        }

        //public int InserttblContent(tblContent tblContent)
        //{
        //    DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
        //    int inumOfRowEffect = 0;
        //    SqlParameter[] parameters = new SqlParameter[]{
        //                                                        new SqlParameter("@ID", SqlDbType.Int),
        //                                                        new SqlParameter("@ID_Content", SqlDbType.Int),																															
        //                                                        new SqlParameter("@LinkContent", SqlDbType.NVarChar,500),
        //                                                        new SqlParameter("@LinkImage", SqlDbType.NVarChar,300),
        //                                                        new SqlParameter("@ImageHome", SqlDbType.NVarChar,300),
        //                                                        new SqlParameter("@ChannelID", SqlDbType.Int),
        //                                                        new SqlParameter("@CreateDate", SqlDbType.SmallDateTime),
        //                                                        new SqlParameter("@Status", SqlDbType.Int),
        //                                                        new SqlParameter("@Title", SqlDbType.NVarChar,200),
        //                                                        new SqlParameter("@Header", SqlDbType.NVarChar,200),
        //                                                        new SqlParameter("@Content", SqlDbType.NText),
        //                                                        new SqlParameter("@DatePage", SqlDbType.NVarChar,80),
        //                                                        new SqlParameter("@Ihost", SqlDbType.Bit),
        //                                                        new SqlParameter("@Inew", SqlDbType.Bit),
        //                                                        new SqlParameter("@Count",SqlDbType.Int),
        //                                                        new SqlParameter("@RealDate",SqlDbType.DateTime),
                                                               
        //                                                    };
        //    parameters[0].Direction = ParameterDirection.Output;
        //    parameters[1].Value = tblContent.ID_Content;
        //    parameters[2].Value = tblContent.LinkContent;
        //    parameters[3].Value = tblContent.LinkImage;
        //    parameters[4].Value = tblContent.LinkImage;
        //    parameters[5].Value = tblContent.ImageHome;
        //    parameters[6].Value = tblContent.ChannelID;
        //    parameters[7].Value = tblContent.CreateDate;
        //    parameters[8].Value = tblContent.Status;
        //    parameters[9].Value = tblContent.Title;
        //    parameters[10].Value = tblContent.Header;
        //    parameters[11].Value = tblContent.Content;
        //    parameters[12].Value = tblContent.DatePage;
        //    parameters[13].Value = tblContent.Ihost;
        //    parameters[14].Value = tblContent.Inew;
        //    parameters[15].Value = tblContent.Count;
        //    parameters[16].Value = tblContent.RealDate;



        //    try
        //    {
        //        inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_InserttblContent", CommandType.StoredProcedure, parameters);
        //        tblContent.ID = Convert.ToInt32(parameters[0].Value);
        //    }
        //    catch (Exception ex)
        //    {
        //        tblContent.ID = -1;
        //    }
        //    return tblContent.ID;
        //}

        public bool UpdatetblContent(tblContent tblContent)
        {
            bool bresult = false;
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            int inumOfRowEffect = 0;
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@ID", SqlDbType.Int),
                                                                new SqlParameter("@ID_Content", SqlDbType.Int),																															
                                                                new SqlParameter("@LinkContent", SqlDbType.NVarChar,500),
                                                                new SqlParameter("@LinkImage", SqlDbType.NVarChar,300),
                                                                new SqlParameter("@ImageHome", SqlDbType.NVarChar,300),
                                                                new SqlParameter("@ChannelID", SqlDbType.Int),
                                                                new SqlParameter("@CreateDate", SqlDbType.SmallDateTime),
                                                                new SqlParameter("@Status", SqlDbType.Int),
                                                                new SqlParameter("@Title", SqlDbType.NVarChar,200),
                                                                new SqlParameter("@Header", SqlDbType.NVarChar,200),
                                                                new SqlParameter("@Content", SqlDbType.NText),
                                                                new SqlParameter("@DatePage", SqlDbType.NVarChar,80),
                                                                new SqlParameter("@Ihost", SqlDbType.Bit),
                                                                new SqlParameter("@Inew", SqlDbType.Bit),
                                                                new SqlParameter("@Visited",SqlDbType.Int),
                                                                new SqlParameter("@Ngay",SqlDbType.DateTime),
															};
            parameters[0].Value = tblContent.ID;
            parameters[1].Value = tblContent.ID_Content;
            parameters[2].Value = tblContent.LinkContent;
            parameters[3].Value = tblContent.LinkImage;
          
            parameters[4].Value = tblContent.ImageHome;
            parameters[5].Value = tblContent.ChannelID;
            parameters[6].Value = tblContent.CreateDate;
            parameters[7].Value = tblContent.Status;
            parameters[8].Value = tblContent.Title;
            parameters[9].Value = tblContent.Header;
            parameters[10].Value = tblContent.Content;
            parameters[11].Value = tblContent.DatePage;
            parameters[12].Value = tblContent.Ihost;
            parameters[13].Value = tblContent.Inew;
            parameters[14].Value = tblContent.Visited;
            parameters[15].Value = tblContent.Ngay;

            try
            {
                inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_UpdatetblContent", CommandType.StoredProcedure, parameters);
                bresult = true;
            }
            catch (Exception ex)
            {
                bresult = false;
            }
            return bresult;
        }

        public tblContent SelecttblContentByID(int id)
        {
            tblContent tblContent = new tblContent();
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@ID", SqlDbType.Int),
															};
            parameters[0].Value = id;
            using (SqlConnection conn = new SqlConnection(DataHelper.GetConnectionString()))
            {
                IDataReader dr;
                SqlCommand sqlCommand = new SqlCommand("sp_SelecttblContent", conn);
                sqlCommand.Parameters.AddRange(parameters);
                sqlCommand.CommandType = CommandType.StoredProcedure;
                conn.Open();
                dr = sqlCommand.ExecuteReader();
                while (dr.Read())
                {
                    tblContent.ID = Convert.ToInt32(dr["ID"].ToString());
                    tblContent.ID_Content = Convert.ToInt32(dr["ID_Content"].ToString());
                    tblContent.LinkContent = (dr["LinkContent"].ToString());
                    tblContent.LinkImage = (dr["LinkImage"].ToString());
                    tblContent.ImageHome = (dr["ImageHome"].ToString());
                    tblContent.ChannelID = Convert.ToInt32(dr["ChannelID"].ToString());
                    tblContent.CreateDate = Convert.ToDateTime(dr["CreateDate"].ToString());
                    tblContent.Status = Convert.ToInt32(dr["Status"].ToString());
                    tblContent.Title = (dr["Title"].ToString());
                    tblContent.Header = (dr["Header"].ToString());
                    tblContent.Content = (dr["Content"].ToString());
                    tblContent.DatePage = (dr["DatePage"].ToString());
                    tblContent.Ihost = Convert.ToByte(dr["Ihost"]);
                    tblContent.Inew = Convert.ToByte(dr["Inew"]);
                    tblContent.Visited = Convert.ToInt32(dr["Visited"].ToString());
                    tblContent.Ngay = Convert.ToDateTime(dr["Ngay"].ToString());
                }
                dr.Close();
            }

            return tblContent;
        }

        public DataTable SelecttblContentDynamic(string Where, string Order)
        {
            DataSet ds = new DataSet();
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@WhereCondition", SqlDbType.NVarChar,500),
																new SqlParameter("@OrderByExpression", SqlDbType.NVarChar,250),
															};
            parameters[0].Value = Where;
            parameters[1].Value = Order;
            try
            {
                ds = dataProvider.ExecuteDataSet("sp_SelecttblContentDynamic", CommandType.StoredProcedure, parameters);
            }
            catch (Exception ex)
            {
                return null;
            }
            return ds.Tables["Table"];
        }

        public DataTable SelecttblContentDynamic_top(string Top, string Where, string Order)
        {
            DataSet ds = new DataSet();
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            SqlParameter[] parameters = new SqlParameter[]{
                                                                new SqlParameter("@Top", SqlDbType.NVarChar,20),
																new SqlParameter("@WhereCondition", SqlDbType.NVarChar,500),
																new SqlParameter("@OrderByExpression", SqlDbType.NVarChar,250),
															};
            parameters[0].Value = Top;
            parameters[1].Value = Where;
            parameters[2].Value = Order;
            try
            {
                ds = dataProvider.ExecuteDataSet("sp_SelecttblContentDynamic_Top", CommandType.StoredProcedure, parameters);
            }
            catch (Exception ex)
            {
                return null;
            }
            return ds.Tables["Table"];
        }

    }
}
